Slow transfer speeds

--0-1968311245-1154971499=:54335
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi. I'm new at using PostgreSQL. I have found posts related to this one b=
ut there is not a definite answer or solution. Here it goes.
Where I work, all databases were built with MS Access. The Access files a=
re hosted by computers with Windows 2000 and Windows XP. A new server is =
on its way and only Open Source Software is going to be installed. The OS=
is going to be SUSE Linux 10.1 and we are making comparisons between MyS=
QL, PostgreSQL and MS Access. We installed MySQL and PostgreSQL on both S=
USE and Windows XP (MySQL & PostgreSQL DO NOT run at the same time)(There=
is one HDD for Windows and one for Linux)
The "Test Server" in which we install the DBMS has the following characte=
ristics:

CPU speed =3D 1.3 GHz
RAM =3D 512 MB
HDD =3D 40 GB

The biggest table has 544371 rows(tuples?) with 55 rows. All fields are f=
loat8. Only 1 is varchar(255) and 1 timestamp.
We query the MS Access databases through Visual Basic Programs and ODBC D=
rivers. We made a Visual Basic program that uses ADO to connect to ALL th=
ree DBMS using ODBC drivers.

When we run the following query "SELECT * FROM big_table", we get the fol=
lowing resutls:

MS Access
- Execution time ~ 51 seconds (Depending on the client machine, it can go=
as low as 20 seconds)
- Network Utilization ~ 75 Mbps (According to Windows Task Manager)

MySQL 5.0(under Windows)
- Execution time ~ 630 seconds
- Network Utilization ~ 8 Mbps

PostgreSQL 8.1(under Windows)
- Execution time ~ 290 seconds)
- Network Utilization ~ 13 Mbps


MS Access (under Linux. MS Access files are in the Linux computer which h=
as the SAMBA server running. The client computer has a mapped network dri=
ve that conects to the Linux files.)
- Execution time ~ 55 seconds (Depending on the client machine, it can go=
as low as 20 seconds)
- Network Utilization ~ 70 Mbps (According to Windows Task Manager)

MySQL 5.0(under Linux)
- Execution time ~ 440 seconds
- Network Utilization ~ 11 Mbps

PostgreSQL 8.1(under Linux)
- Execution time ~ 180 seconds)
- Network Utilization ~ 18 Mbps

Due to the fact that the query returns a lot of rows, I cannot use the OD=
BC driver with the "Use Declare/Fetch" option disabled. If I run the quer=
y with this option disabled, the transfer speed goes up to about 20 Mpbs =
(PostgreSQL in Windows) and ~35 Mbps (PostgreSQL in Linux) (The transfer =
speed never goes beyond 40 Mbps even if we query from several clients at =
the same time. If we query MS Access from several machines, the transfer =
speed goes almost to 85 Mbps. Obviously, these simultaneous querys run sl=
ower). The problem with running the query with the "Use Declare/Fetch" op=
tion disabled is that the client computer shows an error saying "Out of m=
emory while reading tuples".

Very different results are obtained if a the query "SELECT * from big_tab=
le ORDER BY "some_column"". In this scenario PostgreSQL is faster than MS=
Access or MySQL by more than 100 seconds. Transfer speed, however, trans=
fer speed is still slower for PostgreSQL than for MS Access.

We have run many other queries (not complex, at most nesting of 5 inner j=
oins) and MS Access is always faster. We have seen by looking at the netw=
ork activity in the Windows Task Manager that the main problem is the tra=
nsfer speed. We also have noticed that MS Access quickly downloads the fi=
le that has the necesary information and works on it locally on the clien=
t computer. The queries, obviously, run faster if the client computer has=
more resources (CPU speed, RAM, etc.). The fact that the client computer=
does not use any resource to execute the query, only to receive the resu=
lts, is one big plus for PostgreSQL (we think). We need,however, to impro=
ve the performance of the queries that return a lot of rows because those=
are the most used queries.

We searched the postgresql archives, mailing lists, etc. and have tried c=
hanging the parameters of the PostgreSQL server(both on Linux and Windows=
)(We also tried with the default parameters) and changing the parameters =
of the ODBC driver as suggested. We still get aproximately the same resul=
ts. We have even changed some TCP/IP parameters(only in Windows) but no i=
mprovement.

We have turned off all tracings, logs, and debugs of the ODBC driver. The=
behaviour is the same when querying from pgAdmin III.

To get to the point: Is this problem with the transfer rates a PostgreSQL=
server/PostgresQL ODBC driver limitation?
Is there a way to increase the transfer rates?

Thank you very much for any help received!

Hansell E. Baran Altuve
=09
---------------------------------
Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ co=
untries) for 2=A2/min or less.
--0-1968311245-1154971499=:54335
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi. I'm new at using PostgreSQL. I have found posts related to this one b=
ut there is not a definite answer or solution. Here it goes.
Where I w=
ork, all databases were built with MS Access. The Access files are hosted=
by computers with Windows 2000 and Windows XP. A new server is on its wa=
y and only Open Source Software is going to be installed. The OS is going=
to be SUSE Linux 10.1 and we are making comparisons between MySQL, Postg=
reSQL and MS Access. We installed MySQL and PostgreSQL on both SUSE and W=
indows XP (MySQL & PostgreSQL DO NOT run at the same time)(There is o=
ne HDD for Windows and one for Linux)
The "Test Server" in which we in=
stall the DBMS has the following characteristics:

CPU speed =3D 1.=
3 GHz
RAM =3D 512 MB
HDD =3D 40 GB

The biggest table has 544=
371 rows(tuples?) with 55 rows. All fields are float8. Only 1 is varchar(=
255) and 1 timestamp.
We query the MS Access databases through Visual =
Basic Programs and ODBC Drivers. We made a
Visual Basic program that uses ADO to connect to ALL three DBMS using OD=
BC drivers.

When we run the following query "SELECT * FROM big_tab=
le", we get the following resutls:

MS Access
- Execution time ~=
51 seconds (Depending on the client machine, it can go as low as 20 seco=
nds)
- Network Utilization ~ 75 Mbps (According to Windows Task Manage=
r)

MySQL 5.0(under Windows)
- Execution time ~ 630 seconds
-=
Network Utilization ~ 8 Mbps

PostgreSQL 8.1(under Windows)
- E=
xecution time ~ 290 seconds)
- Network Utilization ~ 13 Mbps

r>MS Access (under Linux. MS Access files are in the Linux computer which=
has the SAMBA server running. The client computer has a mapped network d=
rive that conects to the Linux files.)
- Execution time ~ 55 seconds (=
Depending on the client machine, it can go as low as 20 seconds)
- Net=
work Utilization ~ 70 Mbps (According to Windows Task Manager)

MyS=
QL 5.0(under Linux)
- Execution time
~ 440 seconds
- Network Utilization ~ 11 Mbps

PostgreSQL 8.1(u=
nder Linux)
- Execution time ~ 180 seconds)
- Network Utilization ~=
18 Mbps

Due to the fact that the query returns a lot of rows, I c=
annot use the ODBC driver with the "Use Declare/Fetch" option disabled. I=
f I run the query with this option disabled, the transfer speed goes up t=
o about 20 Mpbs (PostgreSQL in Windows) and ~35 Mbps (PostgreSQL in Linux=
) (The transfer speed never goes beyond 40 Mbps even if we query from sev=
eral clients at the same time. If we query MS Access from several machine=
s, the transfer speed goes almost to 85 Mbps. Obviously, these simultaneo=
us querys run slower). The problem with running the query with the "Use D=
eclare/Fetch" option disabled is that the client computer shows an error =
saying "Out of memory while reading tuples".

Very different result=
s are obtained if a the query "SELECT * from big_table ORDER BY "some_col=
umn"". In this scenario PostgreSQL is
faster than MS Access or MySQL by more than 100 seconds. Transfer speed,=
however, transfer speed is still slower for PostgreSQL than for MS Acces=
s.

We have run many other queries (not complex, at most nesting of=
5 inner joins) and MS Access is always faster. We have seen by looking a=
t the network activity in the Windows Task Manager that the main problem =
is the transfer speed. We also have noticed that MS Access quickly downlo=
ads the file that has the necesary information and works on it locally on=
the client computer. The queries, obviously, run faster if the client co=
mputer has more resources (CPU speed, RAM, etc.). The fact that the clien=
t computer does not use any resource to execute the query, only to receiv=
e the results, is one big plus for PostgreSQL (we think). We need,however=
, to improve the performance of the queries that return a lot of rows bec=
ause those are the most used queries.

We searched the postgresql a=
rchives, mailing lists, etc. and have
tried changing the parameters of the PostgreSQL server(both on Linux and=
Windows)(We also tried with the default parameters) and changing the par=
ameters of the ODBC driver as suggested. We still get aproximately the sa=
me results. We have even changed some TCP/IP parameters(only in Windows) =
but no improvement.

We have turned off all tracings, logs, and deb=
ugs of the ODBC driver. The behaviour is the same when querying from pgAd=
min III.

To get to the point: Is this problem with the transfer ra=
tes a PostgreSQL server/PostgresQL ODBC driver limitation?
Is there a =
way to increase the transfer rates?

Thank you very much for any he=
lp received!

Hansell E. Baran Altuve

Re: Slow transfer speeds

--0__=0ABBFB50DFF62E1A8f9e8a93df938690918c0ABBFB50DFF62E1A
Content-type: multipart/alternative;
Boundary="1__=0ABBFB50DFF62E1A8f9e8a93df938690918c0ABBFB50DF F62E1A"

--1__=0ABBFB50DFF62E1A8f9e8a93df938690918c0ABBFB50DFF62E1A
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: quoted-printable


You are probably stuck if you are getting the same results with pgAdmin III
as with Access.

That said, the normal DBA steps for speed optimization still can shed
light.

Check the application
1. You did not provide quite enough sample of your ADO code, if that is
what you are using.
Typically you will want to test the effect of:
-using a Server side cursor (rs.CursorLocation =3D adUseServer)
-forcing ForwardOnly /ReadOnly cursors (not Keyset cursoring) ..rs.open
sql, conn, adOpenForwardOnly, adOpenReadonly, adCmdText
-forcing a forward only cursor will give control back to the program
sooner, but will not have a RecordCount, (all the records are not returned
to the buffer necessarily,... just the cursor as a pointer).
If you want all the records back, or a RecordCount before control returns
to your program use adOpenSnapshot.
There is a difference between speed and perceived responsiveness, (see the
CacheSize, CursorType ...adOpenSnaphost, adOpenForwardOnly MSDN
documentation for details)
2. There is also the matter of how many records are fetched at a time
rs.CacheSize (the default is 1 but can be raised effectively to about
16000).

Check the Driver
3. You did not specify which pgodbc driver version you are testing with. I
believe 08.02.02 is recommended.
4. Check the UseDeclareFetch setting (as you have already done.)
5. Check for Logging

Check the Database
6. Check data types. When linked as tables, make sure Access understand
your float8 is the same type as its Double. You don't want to spend
unnecessary time on type translations.
7. Much performance hinges on structure at the database, appropriate
primary keys, and foreign keys, data types, etc.
8. If there are many joins to be done, the joins should be done in a VIEW
on the server to eliminate optimization cycles.
9. You might also want to explore PostgreSQL functions (stored procedures),
again for the elimination of optimization cycles having to do with parsing
your query.
10. Check for how much logging is going on.


Did I mention that you are probably stuck if you get the same results with
pgAdmin as with Access. I think pgAdmin doesn't even use the pgodbc driver,
it uses its own socket to socket connection and the pglib, (last I looked
that is).


630 seconds (over 15 minutes for one half million row query.... geez).



Greg Campbell ENG-ASE/Michelin US5
Lexington, South Carolina
803-951-5561, x75561
Fax: 803-951-5531
greg.campbell@us.michelin.com



=
=20
hansell baran =
=20
=20
om> To=
=20
Sent by: pgsql-odbc@postgresql.org =
=20
pgsql-odbc-owner@ cc=
=20
postgresql.org =
=20
Subject=
=20
[ODBC] Slow transfer speeds =
=20
08/07/2006 13:24 =
=20
=
=20
=
=20
=
=20
=
=20
=
=20




Hi. I'm new at using PostgreSQL. I have found posts related to this one but
there is not a definite answer or solution. Here it goes.
Where I work, all databases were built with MS Access. The Access files are
hosted by computers with Windows 2000 and Windows XP. A new server is on
its way and only Open Source Software is going to be installed. The OS is
going to be SUSE Linux 10.1 and we are making comparisons between MySQL,
PostgreSQL and MS Access. We installed MySQL and PostgreSQL on both SUSE
and Windows XP (MySQL & PostgreSQL DO NOT run at the same time)(There is
one HDD for Windows and one for Linux)
The "Test Server" in which we install the DBMS has the following
characteristics:

CPU speed =3D 1.3 GHz
RAM =3D 512 MB
HDD =3D 40 GB

The biggest table has 544371 rows(tuples?) with 55 rows. All fields are
float8. Only 1 is varchar(255) and 1 timestamp.
We query the MS Access databases through Visual Basic Programs and ODBC
Drivers. We made a Visual Basic program that uses ADO to connect to ALL
three DBMS using ODBC drivers.

When we run the following query "SELECT * FROM big_table", we get the
following resutls:

MS Access
- Execution time ~ 51 seconds (Depending on the client machine, it can go
as low as 20 seconds)
- Network Utilization ~ 75 Mbps (According to Windows Task Manager)

MySQL 5.0(under Windows)
- Execution time ~ 630 seconds
- Network Utilization ~ 8 Mbps

PostgreSQL 8.1(under Windows)
- Execution time ~ 290 seconds)
- Network Utilization ~ 13 Mbps


MS Access (under Linux. MS Access files are in the Linux computer which has
the SAMBA server running. The client computer has a mapped network drive
that conects to the Linux files.)
- Execution time ~ 55 seconds (Depending on the client machine, it can go
as low as 20 seconds)
- Network Utilization ~ 70 Mbps (According to Windows Task Manager)

MySQL 5.0(under Linux)
- Execution time ~ 440 seconds
- Network Utilization ~ 11 Mbps

PostgreSQL 8.1(under Linux)
- Execution time ~ 180 seconds)
- Network Utilization ~ 18 Mbps

Due to the fact that the query returns a lot of rows, I cannot use the ODBC
driver with the "Use Declare/Fetch" option disabled. If I run the query
with this option disabled, the transfer speed goes up to about 20 Mpbs
(PostgreSQL in Windows) and ~35 Mbps (PostgreSQL in Linux) (The transfer
speed never goes beyond 40 Mbps even if we query from several clients at
the same time. If we query MS Access from several machines, the transfer
speed goes almost to 85 Mbps. Obviously, these simultaneous querys run
slower). The problem with running the query with the "Use Declare/Fetch"
option disabled is that the client computer shows an error saying "Out of
memory while reading tuples".

Very different results are obtained if a the query "SELECT * from big_table
ORDER BY "some_column"". In this scenario PostgreSQL is faster than MS
Access or MySQL by more than 100 seconds. Transfer speed, however, transfer
speed is still slower for PostgreSQL than for MS Access.

We have run many other queries (not complex, at most nesting of 5 inner
joins) and MS Access is always faster. We have seen by looking at the
network activity in the Windows Task Manager that the main problem is the
transfer speed. We also have noticed that MS Access quickly downloads the
file that has the necesary information and works on it locally on the
client computer. The queries, obviously, run faster if the client computer
has more resources (CPU speed, RAM, etc.). The fact that the client
computer does not use any resource to execute the query, only to receive
the results, is one big plus for PostgreSQL (we think). We need,however, to
improve the performance of the queries that return a lot of rows because
those are the most used queries.

We searched the postgresql archives, mailing lists, etc. and have tried
changing the parameters of the PostgreSQL server(both on Linux and
Windows)(We also tried with the default parameters) and changing the
parameters of the ODBC driver as suggested. We still get aproximately the
same results. We have even changed some TCP/IP parameters(only in Windows)
but no improvement.

We have turned off all tracings, logs, and debugs of the ODBC driver. The
behaviour is the same when querying from pgAdmin III.

To get to the point: Is this problem with the transfer rates a PostgreSQL
server/PostgresQL ODBC driver limitation?
Is there a way to increase the transfer rates?

Thank you very much for any help received!

Hansell E. Baran Altuve


Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+
countries) for 2=A2/min or less.


--1__=0ABBFB50DFF62E1A8f9e8a93df938690918c0ABBFB50DFF62E1A
Content-type: text/html; charset=ISO-8859-1
Content-Disposition: inline
Content-transfer-encoding: quoted-printable

You are probably stuck if you are getting the same resu=
lts with pgAdmin III as with Access.



That said, the normal DBA steps for speed optimization sti=
ll can shed light.



Check the application

1. You did not provide quite enough sample of your ADO cod=
e, if that is what you are using.

Typically you will want to test the effect of:

-using a Server side cursor (rs.CursorLocation =3D adUseSe=
rver)

-forcing ForwardOnly /ReadOnly cursors (not Keyset cursori=
ng) ..rs.open sql, conn, adOpenForwardOnly, adOpenReadonly, adCmdText >

-forcing a forward only cursor will give control back to t=
he program sooner, but will not have a RecordCount, (all the records are no=
t returned to the buffer necessarily,... just the cursor as a pointer). nt>

If you want all the records back, or a RecordCount before =
control returns to your program use adOpenSnapshot.

There is a difference between speed and perceived responsi=
veness, (see the CacheSize, CursorType ...adOpenSnaphost, adOpenForwardOnly=
MSDN documentation for details)

2. There is also the matter of how many records are fetche=
d at a time rs.CacheSize (the default is 1 but can be raised effectively to=
about 16000).



Check the Driver

3. You did not specify which pgodbc driver version you are=
testing with. I believe 08.02.02 is recommended.

4. Check the UseDeclareFetch setting (as you have already =
done.)

5. Check for Logging



Check the Database

6. Check data types. When linked as tables, make sure Acce=
ss understand your float8 is the same type as its Double. You don't want to=
spend unnecessary time on type translations.

7. Much performance hinges on structure at the database, a=
ppropriate primary keys, and foreign keys, data types, etc.

8. If there are many joins to be done, the joins should be=
done in a VIEW on the server to eliminate optimization cycles.

9. You might also want to explore PostgreSQL functions (st=
ored procedures), again for the elimination of optimization cycles having t=
o do with parsing your query.

10. Check for how much logging is going on.





Did I mention that you are probably stuck if you get the s=
ame results with pgAdmin as with Access. I think pgAdmin doesn't even use t=
he pgodbc driver, it uses its own socket to socket connection and the pglib=
, (last I looked that is).





630 seconds (over 15 minutes for one half million row quer=
y.... geez).







Greg Campbell ENG-ASE/Michelin US5

Lexington, South Carolina

803-951-5561, x75561

Fax: 803-951-5531

greg.campbell@us.michelin.com



6" height=3D"16" alt=3D"Inactive hide details for hansell baran <hansell=
b@yahoo.com>">hansell baran <hansellb@yahoo.com><=
/font>

62E1A8f9e8a93df9@michelin.com); background-repeat: no-repeat; " width=3D"40= %"> hansell baran <hansellb@yahoo.com>= Sent by: pgsql-odbc-owner@postgresql.org 08/07/2006 13:24

8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> To "> =3D"0" height=3D"1" width=3D"1" alt=3D""> pgsql-odbc@postgresql.org 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> cc "> =3D"0" height=3D"1" width=3D"1" alt=3D""> 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> Subject "100%"> der=3D"0" height=3D"1" width=3D"1" alt=3D""> [ODBC] Slow transfer speeds 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""> td> helin.com" border=3D"0" height=3D"1" width=3D"1" alt=3D"">